using System;
using System.Collections.Generic;
using System.Text;
using Emlyn.Common.Data.DBUpdater;
using Emlyn.Common.Data.SqlServer.SqlManagers;

namespace Emlyn.Website.EmlynORegan.DBUpdateRules
{
    public class DUR_0030_tblEOArticleList: IDBUpdaterRule
    {
        #region IDBUpdaterRule Members

        public int UpgradeFromVersion()
        {
            return 20;
        }

        public int UpgradeToVersion()
        {
            return 30;
        }

        public void PerformUpgrade()
        {
            string ltblEOArticleList =
                "CREATE TABLE [dbo].[tblEOArticleList] ( " +
                "	[ArticleListID] [uniqueidentifier] NOT NULL , " +
                "	[ArticleListCode] [char] (20) COLLATE Latin1_General_CI_AS NOT NULL , " +
                "	[ArticleListName] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL , " +
                "	[ArticleListDescription] [text] COLLATE Latin1_General_CI_AS NOT NULL  " +
                ") ";
            SqlManager.Execute(ltblEOArticleList);
            string ltblEOArticle_ArticleList =
                "CREATE TABLE [dbo].[tblEOArticle_ArticleList] ( " +
                "	[Article_ArticleListID] [uniqueidentifier] NOT NULL , " +
                "	[ArticleID] [uniqueidentifier] NOT NULL , " +
                "	[ArticleListID] [uniqueidentifier] NOT NULL  " +
                ") ";
            SqlManager.Execute(ltblEOArticle_ArticleList);


            string ltblEOArticleListPK =
                "ALTER TABLE [dbo].[tblEOArticleList] WITH NOCHECK ADD  " +
                "	CONSTRAINT [PK_tblEOArticleList] PRIMARY KEY  CLUSTERED  " +
                "	( " +
                "		[ArticleListID] " +
                "	) ";
            SqlManager.Execute(ltblEOArticleListPK);
            string ltblEOArticle_ArticleListPK =
                "ALTER TABLE [dbo].[tblEOArticle_ArticleList] WITH NOCHECK ADD  " +
                "	CONSTRAINT [PK_tblEOArticle_ArticleList] PRIMARY KEY  CLUSTERED  " +
                "	( " +
                "		[Article_ArticleListID] " +
                "	)  ";
            SqlManager.Execute(ltblEOArticle_ArticleListPK);

            string lixArticleList_ArticleListCode =
                "CREATE  UNIQUE  INDEX [ixArticleList_ArticleListCode] " +
                "ON [dbo].[tblEOArticleList]([ArticleListCode]) ";
            SqlManager.Execute(lixArticleList_ArticleListCode);

            string lforeignKeys =
                "ALTER TABLE [dbo].[tblEOArticle_ArticleList] ADD  " +
                "	CONSTRAINT [FK_tblEOArticle_ArticleList_tblEOArticle] FOREIGN KEY  " +
                "	( " +
                "		[ArticleID] " +
                "	) REFERENCES [dbo].[tblEOArticle] ( " +
                "		[ArticleID] " +
                "	) ON DELETE CASCADE  ON UPDATE CASCADE , " +
                "	CONSTRAINT [FK_tblEOArticle_ArticleList_tblEOArticleList] FOREIGN KEY  " +
                "	( " +
                "		[ArticleListID] " +
                "	) REFERENCES [dbo].[tblEOArticleList] ( " +
                "		[ArticleListID] " +
                "	) ON DELETE CASCADE  ON UPDATE CASCADE  ";
            SqlManager.Execute(lforeignKeys);
        }

        #endregion
    }
}
